package jsyf.oa.dao.pm;

import com.alibaba.dubbo.common.utils.StringUtils;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import jsyf.oa.entity.crm.ExhibitionEntity;
import jsyf.oa.entity.pm.ProjectEntity;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author: wangmin
 * @date: 2018/10/15 10:57
 */
@Repository
public class ProjectDao extends BaseDao {

    public List<JSONObject> getProjcetList(String level, String projectId, String userId) {
        StringBuilder sbSql = new StringBuilder();

        sbSql.append("SELECT D.MIN_PLAN_DATE,B.NAME PRO_PRINCIPAL_NAME,IFNULL(C.PLAN,0) IS_PLAN,A.* FROM  T_PM_PROJECT A ");
        sbSql.append("LEFT JOIN T_SYS_USER B ON A.PRO_PRINCIPAL=B.ID ");
        sbSql.append("LEFT JOIN ( ");
        sbSql.append("  SELECT PROJECT_ID,1 PLAN FROM T_PM_PLAN ");
        sbSql.append("  WHERE PROJECT_ID IN (SELECT ID FROM T_PM_PROJECT WHERE PRO_LEVEL IN (" + (level.equals("3") ? "3" : "1,2") + ")  ");
        sbSql.append("  ) GROUP BY PROJECT_ID ");
        sbSql.append(") C ON A.ID=C.PROJECT_ID ");
        sbSql.append("LEFT JOIN (");
        sbSql.append("  select project_id,min(date_format(plan_date,'%Y-%m-%d')) MIN_PLAN_DATE ");
        sbSql.append("  from t_pm_plan group by project_id ");
        sbSql.append(") D ON A.ID=D.project_id ");
        sbSql.append("where 1=1 AND A.IS_END='2' ");
        if (StringUtils.isNotEmpty(userId)) {
            sbSql.append("AND A.ID IN ( ");
            sbSql.append("  SELECT ID FROM T_PM_PROJECT WHERE PRO_SN IN (SELECT PRO_SN FROM T_PM_PROJECT WHERE PRO_PRINCIPAL='" + userId + "' OR CREATE_USER_ID='" + userId + "') ");
            sbSql.append(") ");
        }

        if (level.equals("3")) {
            sbSql.append("AND A.PRO_LEVEL IN(3) ");
            sbSql.append("AND A.PARENT_ID = '" + projectId + "' ");
        } else {
            sbSql.append("AND A.PRO_LEVEL IN(1,2) ");
        }
        sbSql.append("ORDER BY A.CREATE_TIME,A.PRO_LEVEL,A.SORT_NO");

        return queryForJsonList(sbSql.toString());
    }

    public List<JSONObject> getLevel1ProjcetList() {
        StringBuilder sbSql = new StringBuilder();
        sbSql.append("SELECT ID,PRO_NAME FROM T_PM_PROJECT WHERE PRO_LEVEL=1 AND IS_END=2 ORDER BY CREATE_TIME DESC ");

        return queryForJsonList(sbSql.toString());
    }

    public List<JSONObject> getLevel1Level2Level3ProjectById(String projectId) {
        StringBuilder sbSql = new StringBuilder();
        sbSql.append("SELECT a.ID id,a.PRO_NAME projectName,A.PRO_PRINCIPAL principal,B.NAME principalName, ");
        sbSql.append("A.PRO_SN projectSn,A.SORT_NO sortNo,A.PRO_LEVEL,A.PARENT_ID,false selected,false changed ");
        sbSql.append("FROM T_PM_PROJECT A ");
        sbSql.append("LEFT JOIN T_SYS_USER B ON A.PRO_PRINCIPAL=B.ID ");
        sbSql.append("WHERE A.PRO_SN IN (SELECT PRO_SN FROM T_PM_PROJECT WHERE ID='" + projectId + "') ");
        sbSql.append("ORDER BY A.PRO_LEVEL,A.SORT_NO ");

        return queryForJsonList(sbSql.toString());
    }

    public List<JSONObject> getLevel2ByLevel1Id(String projectId) {
        StringBuilder sbSql = new StringBuilder();
        sbSql.append("SELECT A.* FROM T_PM_PROJECT A ");
        sbSql.append("WHERE A.PARENT_ID='" + projectId + "' ");
        sbSql.append("ORDER BY A.CREATE_TIME,A.PRO_LEVEL,A.SORT_NO ");

        return queryForJsonList(sbSql.toString());
    }

    public List<JSONObject> getPlanList(String startDate, String endDate, String level, String projectId) {
        StringBuilder sbSql = new StringBuilder();
        sbSql.append("SELECT PROJECT_ID,PLAN_DATE FROM T_PM_PLAN ");
        sbSql.append("WHERE PROJECT_ID IN ( ");
        sbSql.append("  SELECT ID FROM T_PM_PROJECT WHERE PRO_LEVEL IN (" + (level.equals("3") ? "3" : "1,2") + ") ");
        if (level.equals("3"))
            sbSql.append("  AND PARENT_ID = '" + projectId + "' ");
        sbSql.append(") ");
        sbSql.append("AND PLAN_DATE BETWEEN '" + startDate + "' AND '" + endDate + "' ");
        return queryForJsonList(sbSql.toString());
    }

    public List<JSONObject> getTrackList(String startDate, String endDate, String level, String projectId) {
        StringBuilder sbSql = new StringBuilder();
        sbSql.append("SELECT ID,PROJECT_ID,TRACK_DATE,CREATE_USER_ID FROM T_PM_TRACK ");
        sbSql.append("WHERE PROJECT_ID IN ( ");
        sbSql.append("  SELECT ID FROM T_PM_PROJECT WHERE PRO_LEVEL IN (" + (level.equals("3") ? "3" : "1,2") + ") ");
        if (level.equals("3"))
            sbSql.append("  AND PARENT_ID = '" + projectId + "' ");
        sbSql.append(") ");
        sbSql.append("AND TRACK_DATE BETWEEN '" + startDate + "' AND '" + endDate + "' ");
        return queryForJsonList(sbSql.toString());
    }

    public JSONObject getProjectTrackByIdAndTrackDate(String projectId, String trackDate) {
        StringBuilder sbSql = new StringBuilder();
        sbSql.append("SELECT * FROM T_PM_TRACK ");
        sbSql.append("WHERE PROJECT_ID='" + projectId + "' ");
        sbSql.append("AND TRACK_DATE='" + trackDate + "'");

        return queryForJsonObject(sbSql.toString());
    }

    public Integer addProject(ProjectEntity projectEntity) {
        StringBuilder sbSql = new StringBuilder();
        sbSql.append("insert into T_PM_PROJECT ");
        sbSql.append("(PRO_PRINCIPAL,PARENT_ID,PRO_LEVEL,CREATE_USER_ID,ID,PRO_NAME,PRO_SN) ");
        sbSql.append("VALUES ");
        sbSql.append("( '" + (StringUtils.isEmpty(projectEntity.getProPrincipal()) ? "" : projectEntity.getProPrincipal()) + "', ");
        sbSql.append("'" + (StringUtils.isEmpty(projectEntity.getParentId()) ? "" : projectEntity.getParentId()) + "', ");
        sbSql.append((StringUtils.isEmpty(projectEntity.getProLevel()) ? 1 : projectEntity.getProLevel()) + ", ");
        sbSql.append("'" + (StringUtils.isEmpty(projectEntity.getCreateUserId()) ? "" : projectEntity.getCreateUserId()) + "', ");
        sbSql.append("'" + projectEntity.getId() + "', ");
        sbSql.append("'" + (StringUtils.isEmpty(projectEntity.getProName()) ? "" : projectEntity.getProName()) + "', ");
        sbSql.append("'" + (StringUtils.isEmpty(projectEntity.getProSn()) ? "" : projectEntity.getProSn()) + "' ");
        sbSql.append(") ");
        return getJdbcTemplate().update(sbSql.toString());
    }

    public List<JSONObject> getProjectPrincipalList(String projectId) {
        StringBuilder sbSql = new StringBuilder();
        sbSql.append("SELECT A.NAME FROM T_SYS_USER A ");
        sbSql.append("JOIN (");
        sbSql.append("  SELECT PRO_PRINCIPAL FROM T_PM_PROJECT WHERE PARENT_ID='" + projectId + "'  ");
        sbSql.append("  GROUP BY PRO_PRINCIPAL");
        sbSql.append(") B ON A.ID=B.PRO_PRINCIPAL");

        return queryForJsonList(sbSql.toString());
    }

    public JSONObject getTrackInfoById(String projectId) {
        StringBuilder sbSql = new StringBuilder();
        sbSql.append("select min(DATE_FORMAT(plan_date,'%Y-%m-%d')) sDate,max(DATE_FORMAT(plan_date,'%Y-%m-%d')) eDate from t_pm_plan where project_id='" + projectId + "';");
        return queryForJsonObject(sbSql.toString());
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, ProjectEntity.class);
    }

    public Integer setProjectFinish(String projectSn) {
        StringBuilder sbSql = new StringBuilder();
        sbSql.append("UPDATE T_PM_PROJECT SET IS_END=1 WHERE PRO_SN='" + projectSn + "'");
        return getJdbcTemplate().update(sbSql.toString());
    }

    public Integer delProjectByIds(String ids) {
        StringBuilder sbSql = new StringBuilder();
        sbSql.append("DELETE FROM T_PM_PROJECT WHERE ID IN (" + ids + ") OR PARENT_ID IN (" + ids + ")");
        return delete(sbSql.toString(), null);
    }

    public JSONObject getProjectInfoById(String id) {
        StringBuilder sbSql = new StringBuilder();
        sbSql.append("select b.PRO_PRINCIPAL upUser,d.NAME upUserName,a.PRO_PRINCIPAL thisUser, ");
        sbSql.append("c.NAME thisUserName,a.PRO_NAME projectName from t_pm_project a ");
        sbSql.append("left join t_pm_project b on a.PARENT_ID=b.ID ");
        sbSql.append("left join t_sys_user c on a.PRO_PRINCIPAL=c.ID ");
        sbSql.append("left join t_sys_user d on b.PRO_PRINCIPAL=d.ID ");
        sbSql.append("where a.id='" + id + "' ");
        return queryForJsonObject(sbSql.toString());
    }
}
